Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OracleParameter Class

An `OracleParameter` object represents a parameter for an `OracleCommand` or a `DataSet` column.

Class Inheritance

`Object`

`MarshalByRefObject`

`OracleParameter`

Declaration

    // C#
    public sealed class OracleParameter : MarshalByRefObject, IDBDataParameter,
       IDataParameter, IDisposable, ICloneable

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Exceptions

`ArgumentException` - The type binding is invalid.

Example

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;

    class OracleParameterSample
    {
      static void Main()
      {
        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleParameter[] prm = new OracleParameter[3];

        // Create OracleParameter objects through OracleParameterCollection
        OracleCommand cmd = con.CreateCommand();

        cmd.CommandText = "select max(empno) from emp";
        int maxno = int.Parse(cmd.ExecuteScalar().ToString());

        prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal,
          maxno + 10, ParameterDirection.Input);
        prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2,
          "Client", ParameterDirection.Input);
        prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal,
          10, ParameterDirection.Input);
        cmd.CommandText =
          "insert into emp(empno, ename, deptno) values(:1, :2, :3)";
        cmd.ExecuteNonQuery();

        Console.WriteLine("Record for employee id {0} has been inserted.",
                          maxno + 10);
      }
    }

Requirements

Namespace: `Oracle.DataAccess.Client`

Assembly: `Oracle.DataAccess.dll`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Members OracleParameter Constructors OracleParameter Static Methods OracleParameter Properties OracleParameter Public Methods

## OracleParameter Members

`OracleParameter` members are listed in the following tables:

OracleParameter Constructors

`OracleParameter` constructors are listed in Table 5-69.

Table 5-69 OracleParameter Constructors

Constructor | Description
--- | ---
OracleParameter Constructors | Instantiates a new instance of `OracleParameter` class (Overloaded)

OracleParameter Static Methods

`OracleParameter` static methods are listed in Table 5-70.

Table 5-70 OracleParameter Static Methods

Methods | Description
--- | ---
`Equals` | Inherited from `Object` (Overloaded)

OracleParameter Properties

`OracleParameter` properties are listed in Table 5-71.

Table 5-71 OracleParameter Properties

Name | Description
--- | ---
ArrayBindSize | Specifies the input or output size of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
ArrayBindStatus | Specifies the input or output status of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
CollectionType | Specifies whether or not the `OracleParameter` represents a collection, and if so, specifies the collection type
DbType | Specifies the datatype of the parameter using the `Data.DbType` enumeration type
Direction | Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter
`IsNullable` | This method is a no-op
Offset | Specifies the offset to the `Value` property or offset to the elements in the `Value` property
OracleDbType | Specifies the Oracle datatype
ParameterName | Specifies the name of the parameter
Precision | Specifies the maximum number of digits used to represent the `Value` property
Scale | Specifies the number of decimal places to which `Value` property is resolved
Size | Specifies the maximum size, in bytes or characters, of the data transmitted to or from the database. For PL/SQL Associative Array Bind, `Size` specifies the maximum number of elements in PL/SQL Associative Array.
SourceColumn | Specifies the name of the `DataTable` Column of the `DataSet`
SourceVersion | Specifies the `DataRowVersion` value to use when loading the `Value` property of the parameter
Status | Indicates the status of the execution related to the data in the `Value` property
Value | Specifies the value of the `Parameter`

OracleParameter Public Methods

`OracleParameter` public methods are listed in Table 5-72.

Table 5-72 OracleParameter Public Methods

Public Method | Description
--- | ---
Clone | Creates a shallow copy of an `OracleParameter` object
`CreateObjRef` | Inherited from `MarshalByRefObject`
Dispose | Releases allocated resources
`Equals` | Inherited from `Object` (Overloaded)
`GetHashCode` | Inherited from `Object`
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
`ToString` | Inherited from `Object` (Overloaded)

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class

## OracleParameter Constructors

`OracleParameter` constructors instantiate new instances of the `OracleParameter` class.

Overload List:

- OracleParameter()

  This constructor instantiates a new instance of `OracleParameter` class.

- OracleParameter (string, OracleDbType)

  This constructor instantiates a new instance of `OracleParameter` class using the supplied parameter name and Oracle datatype.

- OracleParameter(string, object)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name and parameter value.

- OracleParameter(string, OracleDbType, ParameterDirection)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, and parameter direction.

- OracleParameter(string, OracleDbType, object, ParameterDirection)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, value, and direction.

- OracleParameter(string, OracleDbType, int)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, and size.

- OracleParameter(string, OracleDbType, int, string)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, and source column.

- OracleParameter(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, direction, null indicator, precision, scale, source column, source version and parameter value.

- OracleParameter(string, OracleDbType, int, object, ParameterDirection)

  This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, value, and direction.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### OracleParameter()

This constructor instantiates a new instance of `OracleParameter` class.

Declaration

    // C#
    public OracleParameter();

Remarks

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter (string, OracleDbType)

This constructor instantiates a new instance of `OracleParameter` class using the supplied parameter name and Oracle datatype.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType oraType);

Parameters

- `parameterName`

  The parameter name.

- `oraType`

  The datatype of the `OracleParameter`.

Remarks

Changing the `DbType` implicitly changes the `OracleDbType`.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter(string, object)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name and parameter value.

Declaration

    // C#
    public OracleParameter(string parameterName, object obj);

Parameters

- `parameterName`

  The parameter name.

- `obj`

  The value of the `OracleParameter`.

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter(string, OracleDbType, ParameterDirection)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, and parameter direction.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType type,
       ParameterDirection direction);

Parameters

- `parameterName`

  The parameter name.

- `type`

  The datatype of the `OracleParameter`.

- `direction`

  The direction of the `OracleParameter`.

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter(string, OracleDbType, object, ParameterDirection)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, value, and direction.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType type, object obj,
       ParameterDirection direction);

Parameters

- `parameterName`

  The parameter name.

- `type`

  The datatype of the `OracleParameter`.

- `obj`

  The value of the `OracleParameter`.

- `direction`

  The `ParameterDirection` value.

Remarks

Changing the `DbType` implicitly changes the `OracleDbType`.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter(string, OracleDbType, int)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, and size.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType type,
       int size);

Parameters

- `parameterName`

  The parameter name.

- `type`

  The datatype of the `OracleParameter`.

- `size`

  The size of the `OracleParameter` value.

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

### OracleParameter(string, OracleDbType, int, string)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, and source column.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType type, int size,
      string srcColumn);

Parameters

- `parameterName`

  The parameter name.

- `type`

  The datatype of the `OracleParameter`.

- `size`

  The size of the `OracleParameter` value.

- `srcColumn`

  The name of the source column.

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration"

### OracleParameter(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, direction, null indicator, precision, scale, source column, source version and parameter value.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType oraType,
       int size, ParameterDirection direction, bool isNullable, byte
       precision, byte scale,  string srcColumn, DataRowVersion srcVersion,
       object obj);

Parameters

- `parameterName`

  The parameter name.

- `oraType`

  The datatype of the `OracleParameter`.

- `size`

  The size of the `OracleParameter` value.

- `direction`

  The `ParameterDirection` value.

- `isNullable`

  An indicator that specifies if the parameter value can be `null`.

- `precision`

  The precision of the parameter value.

- `scale`

  The scale of the parameter value.

- `srcColumn`

  The name of the source column.

- `srcVersion`

  The `DataRowVersion` value.

- `obj`

  The parameter value.

Exceptions

`ArgumentException` - The supplied value does not belong to the type of `Value` property in any of the `OracleType`s.

Remarks

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration"

### OracleParameter(string, OracleDbType, int, object, ParameterDirection)

This constructor instantiates a new instance of the `OracleParameter` class using the supplied parameter name, datatype, size, value, and direction.

Declaration

    // C#
    public OracleParameter(string parameterName, OracleDbType type, int size,
       object obj, ParameterDirection direction);

Parameters

- `parameterName`

  The parameter name.

- `type`

  The datatype of the `OracleParameter`.

- `size`

  The size of the `OracleParameter` value.

- `obj`

  The value of the `OracleParameter`.

- `direction`

  The `ParameterDirection` value.

Remarks

Changing the `DbType` implicitly changes the `OracleDbType`.

Unless explicitly set in the constructor, all the properties have the default values.

Default Values:

- `DbType` - `String`

- `ParameterDirection` - `Input`

- `isNullable` - `true`

- `offset` - `0`

- `OracleDbType` - `Varchar2`

- `ParameterAlias` - Empty string

- `ParameterName` - Empty string

- `Precision` - `0`

- `Size` - `0`

- `SourceColumn` - Empty string

- `SourceVersion` - `Current`

- `ArrayBindStatus` - `Success`

- `Value` - `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "OracleParameterCollection Class"

## OracleParameter Static Methods

The `OracleParameter` static method is listed in Table 5-73.

Table 5-73 OracleParameter Static Method

Method | Description
--- | ---
`Equals` | Inherited from `Object` (Overloaded)

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

## OracleParameter Properties

`OracleParameter` properties are listed in Table 5-74.

Table 5-74 OracleParameter Properties

Name | Description
--- | ---
ArrayBindSize | Specifies the input or output size of elements in `Value` property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
ArrayBindStatus | Specifies the input or output status of elements in `Value` property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution
CollectionType | Specifies whether or not the `OracleParameter` represents a collection, and if so, specifies the collection type
DbType | Specifies the datatype of the parameter using the `Data.DbType` enumeration type
Direction | Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter
`IsNullable` | This method is a no-op
Offset | Specifies the offset to the `Value` property or offset to the elements in the `Value` property
OracleDbType | Specifies the Oracle datatype
ParameterName | Specifies the name of the parameter
Precision | Specifies the maximum number of digits used to represent the `Value` property
Scale | Specifies the number of decimal places to which `Value` property is resolved
Size | Specifies the maximum size, in bytes or characters, of the data transmitted to or from the database. For PL/SQL Associative Array Bind, `Size` specifies the maximum number of elements in PL/SQL Associative Array
SourceColumn | Specifies the name of the `DataTable` Column of the `DataSet`
SourceVersion | Specifies the `DataRowVersion` value to use when loading the `Value` property of the parameter
Status | Indicates the status of the execution related to the data in the `Value` property
Value | Specifies the value of the `Parameter`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### ArrayBindSize

This property specifies the maximum size, in bytes or characters, of the data for each array element transmitted to or from the database. This property is used for Array Bind or PL/SQL Associative Array execution.

Declaration

    // C#
    public int[] ArrayBindSize {get; set; }

Property Value

An array of `int` values specifying the size.

Remarks

Default = `null`.

This property is only used for variable size element types for an Array Bind or PL/SQL Associative Array. For fixed size element types, this property is ignored.

Each element in the `ArrayBindSize` corresponds to the bind size of an element in the `Value` property. Before execution, `ArrayBindSize` specifies the maximum size of each element to be bound in the `Value` property. After execution, it contains the size of each element returned in the `Value` property.

For binding a PL/SQL Associative Array, whose elements are of a variable-length element type, as an `InputOutput`, `Out`, or `ReturnValue` parameter, this property must be set properly. The number of elements in `ArrayBindSize` must be equal to the value specified in the `OracleParameter.Size` property.

Example

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class ArrayBindSizeSample
    {
      static void Main()
      {
        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleParameter[] prm = new OracleParameter[3];

        // Create OracleParameter objects through OracleParameterCollection
        OracleCommand cmd = con.CreateCommand();

        cmd.CommandText = "select max(empno) from emp";
        int maxno = int.Parse(cmd.ExecuteScalar().ToString());

        // Set the ArrayBindCount for Array Binding
        cmd.ArrayBindCount = 2;

        prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal,
          new int[2] {maxno + 10, maxno + 11}, ParameterDirection.Input);
        prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2,
          new string[2] {"Client1xxx", "Client2xxx"}, ParameterDirection.Input);
        prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal,
          new int[2] {10, 10}, ParameterDirection.Input);

        // Set the ArrayBindSize for prm[1]
        // These sizes indicate the maximum size of the elements in Value property
        prm[1].ArrayBindSize = new int[2];
        prm[1].ArrayBindSize[0] = 7; // Set ename = "Client1"
        prm[1].ArrayBindSize[1] = 7; // Set ename = "Client2"

        cmd.CommandText =
          "insert into emp(empno, ename, deptno) values(:1, :2, :3)";

        cmd.ExecuteNonQuery();

        Console.WriteLine("Record for employee id {0} has been inserted.",
          maxno + 10);
        Console.WriteLine("Record for employee id {0} has been inserted.",
          maxno + 11);

        prm[0].Dispose();
        prm[1].Dispose();
        prm[2].Dispose();
        cmd.Dispose();

        con.Close();
        con.Dispose();
      }
    }

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "ArrayBindCount" "Size" and "Value" for more information on binding Associative Arrays "ArrayBindStatus"

### ArrayBindStatus

This property specifies the input or output status of each element in the `Value` property before or after an Array Bind or PL/SQL Associative Array execution.

Declaration

    // C#
    public OracleParameterStatus[] ArrayBindStatus { get; set; }

Property Value

An array of `OracleParameterStatus` enumerated values.

Exceptions

`ArgumentOutofRangeException` - The `Status` value specified is invalid.

Remarks

Default = `null`.

`ArrayBindStatus` is used for Array Bind and PL/SQL Associative Array execution only.

Before execution, `ArrayBindStatus` indicates the bind status of each element in the `Value` property. After execution, it contains the execution status of each element in the `Value` property.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "ArrayBindCount" "OracleParameterStatus Enumeration" "Value" for more information on binding Associative Arrays "ArrayBindSize"

### CollectionType

This property specifies whether or not the `OracleParameter` represents a collection, and if so, specifies the collection type.

Declaration

    // C#
    public OracleCollectionType CollectionType { get; set; }

Property Value

An `OracleCollectionType` enumerated value.

Exceptions

`ArgumentException` - The `OracleCollectionType` value specified is invalid.

Remarks

Default = `OracleCollectionType.None`. If `OracleParameter` is used to bind a PL/SQL Associative Array, then `CollectionType` must be set to `OracleCollectionType.PLSQLAssociativeArray`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### DbType

This property specifies the datatype of the parameter using the `Data.DbType` enumeration type.

Declaration

    // C#
    public DbType DbType {get; set; }

Property Value

A `DbType` enumerated value.

Implements

`IDataParameter`

Exceptions

`ArgumentException` - The `DbType` value specified is invalid.

Remarks

Default = `DbType.String`

`DbType` is the datatype of each element in the array if the `OracleParameter` object is used for Array Bind or PL/SQL Associative Array Bind execution.

Due to the link between `DbType` and `OracleDbType` properties, if the `DbType` property is set, the `OracleDbType` property is inferred from `DbType`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "Inference of OracleDbType from DbType" "CollectionType"

### Direction

This property specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter.

Declaration

    // C#
    public ParameterDirection Direction { get; set; }

Property Value

A `ParameterDirection` enumerated value.

Implements

`IDataParameter`

Exceptions

`ArgumentOutOfRangeException` - The `ParameterDirection` value specified is invalid.

Remarks

Default = `ParameterDirection.Input`

Possible values: `Input`, `InputOutput`, `Output`, and `ReturnValue`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### Offset

This property specifies the offset to the `Value` property.

Declaration

    // C#
    public int Offset { get; set; }

Property Value

An `int` that specifies the offset.

Exceptions

`ArgumentOutOfRangeException` - The `Offset` value specified is invalid.

Remarks

Default = `0`

For Array Bind and PL/SQL Associative Array Bind, `Offset` applies to every element in the `Value` property.

The `Offset` property is used for binary and string datatypes. The `Offset` property represents the number of bytes for binary types and the number of characters for strings. The count for strings does not include the terminating character if a `null` is referenced. The `Offset` property is used by parameters of the following types:

- `OracleDbType.BFile`

- `OracleDbType.Blob`

- `OracleDbType.LongRaw`

- `OracleDbType.Raw`

- `OracleDbType.Char`

- `OracleDbType.Clob`

- `OracleDbType.NClob`

- `OracleDbType.NChar`

- `OracleDbType.NVarchar2`

- `OracleDbType.Varchar2`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### OracleDbType

This property specifies the Oracle datatype.

Declaration

    // C#
    public OracleDbType OracleDbType { get; set; }

Property Value

An `OracleDbType` enumerated value.

Remarks

Default = `OracleDbType.Varchar2`

If the `OracleParameter` object is used for Array Bind or PL/SQL Associative Array Bind execution, `OracleDbType` is the datatype of each element in the array.

The `OracleDbType` property and `DbType` property are linked. Therefore, setting the `OracleDbType` property changes the `DbType` property to a supporting `DbType`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleDbType Enumeration" "Inference of DbType from OracleDbType" "CollectionType"

### ParameterName

This property specifies the name of the parameter.

Declaration

    // C#
    public string ParameterName { get; set; }

Property Value

`String`

Implements

`IDataParameter`

Remarks

Default = `null`

Oracle supports `ParameterName` up to 30 characters.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### Precision

This property specifies the maximum number of digits used to represent the `Value` property.

Declaration

    // C#
    Public byte Precision { get; set; }

Property Value

`byte`

Remarks

Default = `0`

The `Precision` property is used by parameters of type `OracleDbType.Decimal`.

Oracle supports `Precision` range from `0` to `38`.

For Array Bind and PL/SQL Associative Array Bind, `Precision` applies to each element in the `Value` property.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class "Value" OracleParameter Members

### Scale

This property specifies the number of decimal places to which `Value` property is resolved.

Declaration

    // C#
    public byte Scale { get; set; }

Property Value

`byte`

Remarks

Default = `0`.

`Scale` is used by parameters of type `OracleDbType.Decimal`.

Oracle supports `Scale` between `-84` and `127`.

For Array Bind and PL/SQL Associative Array Bind, `Scale` applies to each element in the `Value` property.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "Value"

### Size

This property specifies the maximum size, in bytes or characters, of the data transmitted to or from the database.

Declaration

    // C#
    public int Size { get; set;}

Property Value

`int`

Exceptions

`ArgumentOutOfRangeException` - The `Size` value specified is invalid.

`InvalidOperationException` - The `Size` = 0 when the `OracleParameter` object is used to bind a PL/SQL Associative Array.

Remarks

For PL/SQL Associative Array Bind, `Size` specifies the maximum number of elements in PL/SQL Associative Array.

If `Size` is not explicitly set, it is inferred from the actual size of the specified parameter value when binding only for input parameters. Output parameters must have their size defined explicitly.

The default value is `0`.

Before execution, this property specifies the maximum size to be bound in the `Value` property. After execution, it contains the size of the type in the `Value` property.

`Size` is used for parameters of the following types:

- `OracleDbType.Blob`

- `OracleDbType.Char`

- `OracleDbType.Clob`

- `OracleDbType.LongRaw`

- `OracleDbType.NChar`

- `OracleDbType.NClob`

- `OracleDbType.NVarchar2`

- `OracleDbType.Raw`

- `OracleDbType.Varchar2`

The value of `Size` is handled as follows:

- Fixed length datatypes: ignored

- Variable length datatypes: describes the maximum amount of data transmitted to or from the database. For character data, `Size` is in number of characters and for binary data, it is in number of bytes.

If the `Size` is not explicitly set, it is inferred from the actual size of the specified parameter value when binding.

Note: `Size` does not include the null terminating character for the string data.

If the `OracleParameter` object is used to bind a PL/SQL Associative Array, `Size` specifies the maximum number of elements in the PL/SQL Associative Array. Before the execution, this property specifies the maximum number of elements in the PL/SQL Associative Array. After the execution, it specifies the current number of elements returned in the PL/SQL Associative Array. For `Output` and `InputOutput` parameters and return values, `Size` specifies the maximum number of elements in the PL/SQL Associative Array.

ODP.NET does not support binding an empty PL/SQL Associative Array. Therefore, `Size` cannot be set to `0` when the `OracleParameter` object is used to bind a PL/SQL Associative Array.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleDbType Enumeration" "CollectionType" "ArrayBindSize" "ArrayBindStatus" "Value"

### SourceColumn

This property specifies the name of the `DataTable` Column of the `DataSet`.

Declaration

    // C#
    public string SourceColumn { get; set; }

Property Value

A `string`.

Implements

`IDataParameter`

Remarks

Default = empty string

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### SourceVersion

This property specifies the `DataRowVersion` value to use when loading the `Value` property of the parameter.

Declaration

    // C#
    public DataRowVersion SourceVersion { get; set; }

Property Value

`DataRowVersion`

Implements

`IDataParameter`

Exceptions

`ArgumentOutOfRangeException` - The `DataRowVersion` value specified is invalid.

Remarks

Default = `DataRowVersion.Current`

`SourceVersion` is used by the `OracleDataAdapter.UpdateCommand()` during the `OracleDataAdapter.Update` to determine whether the original or current value is used for a parameter value. This allows primary keys to be updated. This property is ignored by the `OracleDataAdapter.InsertCommand()` and the `OracleDataAdapter.DeleteCommand()`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### Status

This property indicates the status of the execution related to the data in the `Value` property.

Declaration

    // C#
    public OracleParameterStatus Status { get; set; }

Property Value

An `OracleParameterStatus` enumerated value.

Exceptions

`ArgumentOutOfRangeException` - The `Status` value specified is invalid.

Remarks

Default = `OracleParameterStatus.Success`

Before execution, this property indicates the bind status related to the `Value` property. After execution, it returns the status of the execution.

`Status` indicates if:

- A `NULL` is fetched from a column.

- Truncation has occurred during the fetch; then `Value` was not big enough to hold the data.

- A `NULL` is to be inserted into a database column; then `Value` is ignored, and a `NULL` is inserted into a database column.

  This property is ignored for Array Bind and PL/SQL Associative Array Bind. Instead, `ArrayBindStatus` property is used.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "OracleParameterStatus Enumeration" "ArrayBindStatus"

### Value

This property specifies the value of the `Parameter`.

Declaration

    // C#
    public object Value { get; set; }

Property Value

An `object`.

Implements

`IDataParameter`

Exceptions

`ArgumentException` - The `Value` property specified is invalid.

`InvalidArgumentException`- The `Value` property specified is invalid.

Remarks

Default = `null`

If the `OracleParameter` object is used for Array Bind or PL/SQL Associative Array, `Value` is an array of parameter values.

The `Value` property can be overwritten by `OracleDataAdapter.Update()`.

The provider attempts to convert any type of value if it supports the `IConvertible` interface. Conversion errors occur if the specified type is not compatible with the value.

When sending a `null` parameter value to the database, the user must specify `DBNull`, not `null`. The `null` value in the system is an empty object that has no value. `DBNull` is used to represent `null` values. The user can also specify a `null` value by setting `Status` to `OracleParameterStatus.NullValue`. In this case, the provider sends a `null` value to the database.

If neither `OracleDbType` nor `DbType` are set, their values can be inferred by `Value`. Please see the following for related information:

- Tables in section "Inference of DbType and OracleDbType from Value"

- "ArrayBindCount"

- "ArrayBindSize"

- "ArrayBindStatus"

- "OracleDbType Enumeration"

For input parameters the value is:

- Bound to the `OracleCommand` that is sent to the database.

- Converted to the datatype specified in `OracleDbType` or `DbType` when the provider sends the data to the database.

For output parameters the value is:

- Set on completion of the `OracleCommand` (true for return value parameters also).

- Set to the data from the database, to the datatype specified in `OracleDbType` or `DbType`.

When array binding is used with:

- Input parameter - `Value` should be set to an array of values. `OracleCommand.ArrayBindCount` should be set to a value that is greater than zero to indicate the number of elements to be bound.

  The number of elements in the array should be equal to the `OracleCommand.ArrayBindCount` property; otherwise, their minimum value is used to bind the elements in the array.

- Output parameter - `OracleCommand.ArrayBindCount` should be set to a value that is greater than zero to indicate the number of elements to be retrieved (for `SELECT` statements).

When PL/SQL Associative Array binding is used with:

- Input parameter – Value should be set to an array of values. `CollectionType` should be set to `OracleCollection.PLSQLAssociativeArray.Size` should be set to specify the possible maximum number of array elements in the PL/SQL Associative Array. If `Size` is smaller than the number of elements in `Value`, then `Size` specifies the number of elements in the Value property to be bound.

- Output parameter - `CollectionType` should be set to `OracleCollection.PLSQLAssociativeArray`. `Size` should be set to specify the maximum number of array elements in PL/SQL Associative Array.

Each parameter should have a value. To bind a parameter with a `null` value, set `Value` to `DBNull.Value`, or set `Status` to `OracleParameterStatus.` `NullInsert`.

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members "ArrayBindCount" "OracleParameterStatus Enumeration"

## OracleParameter Public Methods

`OracleParameter` public methods are listed in Table 5-75.

Table 5-75 OracleParameter Public Methods

Public Method | Description
--- | ---
Clone | Creates a shallow copy of an `OracleParameter` object
`CreateObjRef` | Inherited from `MarshalByRefObject`
Dispose | Releases allocated resources
`Equals` | Inherited from `Object` (Overloaded)
`GetHashCode` | Inherited from `Object`
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
`ToString` | Inherited from `Object` (Overloaded)

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### Clone

This method creates a shallow copy of an `OracleParameter` object.

Declaration

    // C#
    public object Clone();

Return Value

An `OracleParameter` object.

Implements

`ICloneable`

Remarks

The cloned object has the same property values as that of the object being cloned.

Example

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class CloneSample
    {
      static void Main()
      {
        OracleParameter prm1 = new OracleParameter();

        // Prints "prm1.ParameterName = "
        Console.WriteLine("prm1.ParameterName = " + prm1.ParameterName);

        // Set the ParameterName before cloning
        prm1.ParameterName = "MyParam";

        // Clone the OracleParameter
        OracleParameter prm2 = (OracleParameter) prm1.Clone();

        // Prints "prm2.ParameterName = MyParam"
        Console.WriteLine("prm2.ParameterName = " + prm2.ParameterName);

        prm1.Dispose();
        prm2.Dispose();
      }
    }

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members

### Dispose

This method releases resources allocated for an `OracleParameter` object.

Declaration

    // C#
    public void Dispose();

Implements

`IDisposable`

See Also: "Oracle.DataAccess.Client Namespace" OracleParameter Class OracleParameter Members